﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Proyecto7CAD.Properties;
using System.Data;

namespace Proyecto7CAD
{
    public class MisionCAD
    {
        public bool insertarMision(string nombreMision, int mapaMision, string descripcionMision, int inicioMision, int objetivoMision, int dificultadMision, int tabernaMision)
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from Mision ", conex);

                da.Fill(bdvirtual, "mision");
                //Obtenemos la tabla Misiones
                DataTable tabla = new DataTable();
                tabla = bdvirtual.Tables["mision"];
                //Creamos la nueva fila a insertar
                DataRow nuevafila = tabla.NewRow();
                nuevafila[1] = nombreMision;
                nuevafila[2] = mapaMision;
                nuevafila[3] = descripcionMision;
                nuevafila[4] = inicioMision;
                nuevafila[5] = objetivoMision;
                nuevafila[6] = dificultadMision;
                if (tabernaMision == -1)
                    nuevafila[7] = null;
                else
                    nuevafila[7] = tabernaMision;
                nuevafila[8] = objetivoMision;

                tabla.Rows.Add(nuevafila);

                SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
                da.Update(bdvirtual, "mision");

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conex.Close();
            }

            conex.Close();
            return true;
        }

        public bool borrarMision(int id)
        {
            int i = 0;
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from Mision ", conex);

                da.Fill(bdvirtual, "mision");
                //Obtenemos la tabla enemigos
                DataTable tabla = new DataTable();
                tabla = bdvirtual.Tables["mision"];

                foreach (DataRow registro in tabla.Rows)
                {
                    if ((int)registro["idMision"] == id)
                    {
                        tabla.Rows[i].Delete();
                    }
                    i++;
                }

                SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
                da.Update(bdvirtual, "mision");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conex.Close();
            }

            conex.Close();
            return true;
        }

        public DataTable obtenerMision(int index)
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from Mision where idMision=" + index.ToString(), conex);

                da.Fill(bdvirtual, "mision");

                return bdvirtual.Tables["mision"];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conex.Close();
            }
        }

        public DataTable obtenerMisionNombre(string nombre, string nom_des)
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = null;
                if (nom_des == "nombre")
                {
                    da = new SqlDataAdapter("select * from mision where nombreMision LIKE '%" + nombre + "%'", conex);
                }
                else if (nom_des == "descripcion")
                {
                    da = new SqlDataAdapter("select * from mision where descripcionMision LIKE '%" + nombre + "%'", conex);
                }
                da.Fill(bdvirtual, "mision");
                DataTable tabla = new DataTable();
                tabla = bdvirtual.Tables["mision"];

                return tabla;
            }
            catch
            {
                DataTable tablavacia = new DataTable();
                return tablavacia;
            }
            finally
            {
                conex.Close();
            }
        }

        public bool editarMision(int idMision, string nombreMision, int mapaMision, string descripcionMision, int inicioMision, int objetivoMision, int dificultadMision, int tabernaMision)
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);
            bool editado = false;

            try
            {
                int i = 0;
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from Mision", conex);

                da.Fill(bdvirtual, "mision");
                DataTable tabla = new DataTable();
                tabla = bdvirtual.Tables["mision"];

                foreach (DataRow registro in tabla.Rows)
                {
                    if ((int)registro["idMision"] == idMision)
                    {
                        tabla.Rows[i][0] = idMision;
                        tabla.Rows[i][1] = nombreMision;
                        tabla.Rows[i][2] = mapaMision;
                        tabla.Rows[i][3] = descripcionMision;
                        tabla.Rows[i][4] = inicioMision;
                        tabla.Rows[i][5] = objetivoMision;
                        tabla.Rows[i][6] = dificultadMision;
                        tabla.Rows[i][7] = tabernaMision;
                        tabla.Rows[i][8] = objetivoMision;
                        editado = true;
                    }
                    i++;
                }

                SqlCommandBuilder cbuilder = new SqlCommandBuilder(da);
                da.Update(bdvirtual, "mision");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conex.Close();
            }

            conex.Close();
            return editado;
        }

        public DataSet obtenerRecompensas(int index)
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from recompensa where idRecompensa in (Select idRecompensa from MisionRecompensa where idMision =" + index.ToString() + ")", conex);

                da.Fill(bdvirtual, "recompensa");
                return bdvirtual;
            }
            catch (Exception ex)
            {
                DataSet bdvacia = new DataSet();
                throw ex;
            }
        }

        public DataSet obtenerMisiones()
        {
            string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
            SqlConnection conex = new SqlConnection(cadenaConexion);

            try
            {
                DataSet bdvirtual = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter("select * from mision", conex);

                da.Fill(bdvirtual, "mision");
                return bdvirtual;
            }
            catch (Exception ex)
            {
                DataSet bdvacia = new DataSet();
                throw ex;
            }
        }

        public SqlDataReader obtenerMisionesSDRCAD()
        {
            try
            {
                string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
                SqlConnection conex = new SqlConnection(cadenaConexion);

                conex.Open();

                // creamos la query y la ejecutamos
                SqlCommand comando = new SqlCommand("SELECT [nombreMision], [idMision], [mapaMision], [inicioMision], [descripcionMision], [puntosMision], [dificultadMision], [objetivoMision], [imagenMapa] FROM [Mision],[Mapa] WHERE [mapaMision] = [idMapa] ORDER BY [nombreMision]", conex);
                SqlDataReader dr = comando.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //conexion.Close();
            }
        }

        public SqlDataReader obtenerMapaMisionCAD(int id)
        {
            try
            {
                string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
                SqlConnection conex = new SqlConnection(cadenaConexion);

                conex.Open();

                // creamos la query y la ejecutamos
                SqlCommand comando = new SqlCommand("SELECT [nombreMapa],[imagenMapa],[Mapa].[idMapa],[Mision].[puntosMision] FROM [Mision],[Mapa] WHERE [Mision].[idMision]=" + id + " AND [Mapa].[idMapa]=[Mision].[mapaMision] ", conex);
                SqlDataReader dr = comando.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //conexion.Close();
            }
        }

        public SqlDataReader obtenerTabernaMisionCAD(int id)
        {
            try
            {
                string cadenaConexion = Settings.Default.BDProyecto7ConnectionString;
                SqlConnection conex = new SqlConnection(cadenaConexion);

                conex.Open();

                // creamos la query y la ejecutamos
                SqlCommand comando = new SqlCommand("SELECT [tabernaMision] FROM [Mision] WHERE [Mision].[idMision]=" + id , conex);
                SqlDataReader dr = comando.ExecuteReader();
                return dr;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //conexion.Close();
            }
        }
    }
}
